Database migrations

Run the 19 Supabase SQL migrations in order to build the complete TuPerfil.net database schema, RLS policies, and stored procedures.

The database schema is managed as a series of numbered SQL migration files located in supabase/migrations/. Each file is additive — it extends the schema without breaking changes from previous migrations. Run them in order, once, on a fresh Supabase project.

How to run migrations

TuPerfil.net does not use the Supabase CLI for migrations. Instead, paste each file’s contents into the SQL Editor in the Supabase dashboard and click Run:

1. Open the SQL Editor

   In your Supabase project, click SQL Editor in the left sidebar, then click New query.

2. Paste and run each migration in order

   Open supabase/migrations/001_initial_schema.sql in your editor, copy its contents, paste into the SQL Editor, and click Run. Repeat for files 002 through 019, in numeric order.

3. Verify success

   After each migration, the SQL Editor shows a success message and the row count affected. If a migration fails, read the error message — most errors indicate that you ran migrations out of order or skipped one.

Always run migrations in numeric order. Many migrations depend on tables or functions created by earlier ones. Running them out of order will cause errors.

Migration reference

001 — Initial schema

File: 001_initial_schema.sql

Creates the foundational schema for the entire application:

• categories table — id, name, slug, color, created_at
• articles table — id, title, slug, content, excerpt, image_url, category_id, published, views, created_at, updated_at, author_id
• contacts table — id, name, email, message, read, created_at
• update_updated_at() trigger function on articles
• RLS policies for all three tables
• article-images storage bucket with public read and authenticated write policies
• Seeds the six initial categories (Perfil Político, Perfil Judicial, Perfil Salud, Perfil Deportivo, Perfil Regional, Perfil Internacional)

002 — Add featured flag

File: 002_add_featured.sql

Adds a featured BOOLEAN NOT NULL DEFAULT false column to articles, with a partial index (WHERE featured = true) for fast lookups of the featured article used on the homepage.

003 — Update category colors

File: 003_update_category_colors.sql

Updates the color values for all six categories to the final design palette:

Category	New color
Perfil Político	#2563EB
Perfil Judicial	#DC2626
Perfil Salud	#16A34A
Perfil Deportivo	#EAB308
Perfil Regional	#9333EA
Perfil Internacional	#0891B2

004 — Increment views RPC

File: 004_increment_views_rpc.sql

Adds the increment_views(article_id UUID) RPC function. It atomically increments the views counter on an article without a read-modify-write cycle.

This function is superseded by the version in migration 014, which also inserts a row into page_views. The 014 version is what runs in production.

005 — Breaking news

File: 005_breaking_news.sql

Creates the breaking_news table for the urgent banner displayed at the top of the public site. The table is designed for a single active row at a time:

• text TEXT — banner message
• link TEXT — optional destination URL
• active BOOLEAN — whether the banner is visible

Includes the update_updated_at() trigger and RLS (public read, authenticated write). Seeds one inactive row.

006 — Scheduled publishing

File: 006_scheduled_publishing.sql

Adds published_at TIMESTAMPTZ to articles. When NULL, a published article appears immediately. When set to a future timestamp, the article remains hidden until that time passes.

The public read RLS policy is updated to:

USING (
  (published = true AND (published_at IS NULL OR published_at <= now()))
  OR auth.role() = 'authenticated'
)

007 — Author name

File: 007_author_name.sql

Adds author_name TEXT NOT NULL DEFAULT 'Redacción TuPerfil.net' to articles. This avoids a JOIN to auth.users (which has no public display name) when rendering the byline.

008 — Gallery

File: 008_gallery.sql

Adds gallery JSONB NOT NULL DEFAULT '[]' to articles for storing an ordered array of image URLs used in in-article image galleries.

009 — Tags

File: 009_tags.sql

Creates a many-to-many tagging system:

• tags table — id, name (unique), slug (unique), created_at
• article_tags junction table — article_id, tag_id (composite primary key)
• Indexes on both foreign keys and tags.slug
• RLS: public read on both tables, authenticated write

010 — Newsletter subscribers

File: 010_newsletter.sql

Creates the subscribers table for the email newsletter:

• email TEXT UNIQUE — subscriber address
• name TEXT — optional display name
• active BOOLEAN DEFAULT true
• unsubscribed_at TIMESTAMPTZ

RLS allows anonymous users to insert (subscribe), while authenticated users can read, update, and delete.

011 — Comments

File: 011_comments.sql

Creates the comments table with a moderation workflow:

• article_id UUID — parent article (CASCADE on delete)
• author_name TEXT, author_email TEXT
• content TEXT
• approved BOOLEAN DEFAULT false — comments are hidden until an admin approves them

RLS policy summary: anonymous users can INSERT and SELECT approved comments; authenticated users can SELECT all (for moderation), UPDATE (approve/reject), and DELETE.

012 — User roles

File: 012_user_roles.sql

Creates the profiles table linked to auth.users, with a role TEXT CHECK (role IN ('admin', 'editor')).

Key behaviours:

• The handle_new_user() trigger automatically creates a profile row whenever a user is added via Supabase Auth. The very first user in the system receives the admin role; all subsequent users receive editor.
• The is_admin() helper function checks the current user’s role and is used in RLS policies for sensitive operations.
• A backfill INSERT ... SELECT assigns roles to any users that existed before this migration ran.

013 — Ads

File: 013_ads.sql

Creates the ads table for the banner advertising system:

• title TEXT — internal label
• image_url TEXT — ad creative
• link_url TEXT — click destination
• position TEXT DEFAULT 'sidebar' — placement slot (sidebar, header, or between_articles)
• active BOOLEAN DEFAULT true
• sort_order INT DEFAULT 0

RLS: anonymous users can read active ads; authenticated users have full CRUD.

014 — Page views

File: 014_page_views.sql

Creates the page_views table for detailed analytics:

• article_id UUID — the viewed article
• viewed_at TIMESTAMPTZ
• referrer TEXT — raw referrer URL
• referrer_source TEXT — normalized source (direct, google, facebook, twitter, whatsapp, other)

Also updates increment_views() to atomically increment articles.views and insert a row into page_views in one call.

015 — Soft delete

File: 015_soft_delete.sql

Adds deleted_at TIMESTAMPTZ DEFAULT NULL to articles. Articles moved to the trash are not deleted from the database; they are excluded from public queries by the updated RLS policy:

USING (published = true AND deleted_at IS NULL)

Also updates increment_views() to skip soft-deleted articles.

016 — Popular this week RPC

File: 016_popular_this_week.sql

Adds the popular_articles_this_week(lim INT DEFAULT 5) RPC function, which returns the most-viewed articles in the past 7 days from page_views:

CREATE OR REPLACE FUNCTION popular_articles_this_week(lim INT DEFAULT 5)
RETURNS TABLE (
  article_id UUID,
  view_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT pv.article_id, COUNT(*) AS view_count
  FROM page_views pv
  INNER JOIN articles a ON a.id = pv.article_id
  WHERE pv.viewed_at >= now() - interval '7 days'
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY pv.article_id
  ORDER BY view_count DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

017 — Related articles by tags RPC

File: 017_related_by_tags.sql

Adds related_articles_by_tags(p_article_id UUID, p_category_id UUID, lim INT DEFAULT 4), which returns articles that share the most tags with a given article. Results include the full article fields needed to render an ArticleCard, sorted by number of shared tags descending.

018 — Trending 24h RPC

File: 018_trending_24h.sql

Adds trending_articles_24h(lim INT DEFAULT 5) for the “Trending” widget on the homepage and sidebar:

CREATE OR REPLACE FUNCTION trending_articles_24h(lim INT DEFAULT 5)
RETURNS TABLE (
  article_id UUID,
  view_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT pv.article_id, COUNT(*) AS view_count
  FROM page_views pv
  INNER JOIN articles a ON a.id = pv.article_id
  WHERE pv.viewed_at >= now() - interval '24 hours'
    AND a.published = true
    AND a.deleted_at IS NULL
  GROUP BY pv.article_id
  ORDER BY view_count DESC
  LIMIT lim;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

019 — Ad tracking

File: 019_ad_tracking.sql

Creates the ad_events table for impression and click tracking:

• ad_id UUID — references ads(id) (CASCADE on delete)
• event_type TEXT CHECK (event_type IN ('impression', 'click'))

Also adds ad_metrics(days INT DEFAULT 30), an RPC that returns impression and click counts per ad for any time window:

SELECT
  e.ad_id,
  COUNT(*) FILTER (WHERE e.event_type = 'impression') AS impressions,
  COUNT(*) FILTER (WHERE e.event_type = 'click') AS clicks
FROM ad_events e
WHERE e.created_at >= now() - (days || ' days')::interval
GROUP BY e.ad_id;

Anonymous users can INSERT events; authenticated users can SELECT (for the admin analytics dashboard).